﻿<?xml version="1.0" encoding="utf-8" ?>
<?xml-stylesheet type='text/xsl' href="scriptsql.xsl"?>
<root xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database includeDrop="true">dmoz</database>
<procedures>
		
		<procedure owner="dbo" name="usp_Language_Get_List" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets all records from the Language table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[name]
				FROM
					[dbo].[Language]
					
				Select @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_Language_GetPaged" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the Language table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[id]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [id]'
				SET @SQL = @SQL + ', [name]'
				SET @SQL = @SQL + ' FROM [dbo].[Language]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [id],'
				SET @SQL = @SQL + ' [name]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Language]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="usp_Language_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Inserts a record into the Language table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="nvarchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	
				INSERT INTO [dbo].[Language]
					(
					[name]
					)
				VALUES
					(
					@Name
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Language_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Updates a record in the Language table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="nvarchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Language]
				SET
					[name] = @Name
				WHERE
[id] = @Id 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Language_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Deletes a record in the Language table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[Language] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Language_GetById" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Language table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[id],
					[name]
				FROM
					[dbo].[Language]
				WHERE
					[id] = @Id
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_Language_GetByName" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Language table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Name" type="nvarchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[id],
					[name]
				FROM
					[dbo].[Language]
				WHERE
					[name] = @Name
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_Language_Find" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Finds records in the Language table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Name" type="nvarchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [name]
    FROM
	[dbo].[Language]
    WHERE 
	 ([id] = @Id OR @Id is null)
	AND ([name] = @Name OR @Name is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [name]
    FROM
	[dbo].[Language]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([name] = @Name AND @Name is not null)
	Select @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="usp_Language_Find_Dynamic" grant="" skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the Language table using a dynamically generated query.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				
				with PageIndex as (
					select top {3} row_number() over (order by {1}) as RowIndex
					, [id]
					, [name]
					from [dbo].[Language] {0}
				)
				select
				       [id],
				       [name]
				  from PageIndex
				 where RowIndex > {2}
				   and RowIndex <= {3};
				

				-- get total count
				select count(*) as TotalRowCount from [dbo].[Language] {0};
			
				END
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_LocalizedName_Get_List" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets all records from the LocalizedName table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[name],
					[idLanguage]
				FROM
					[dbo].[LocalizedName]
					
				Select @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_LocalizedName_GetPaged" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the LocalizedName table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[id]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [id]'
				SET @SQL = @SQL + ', [name]'
				SET @SQL = @SQL + ', [idLanguage]'
				SET @SQL = @SQL + ' FROM [dbo].[LocalizedName]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [id],'
				SET @SQL = @SQL + ' [name],'
				SET @SQL = @SQL + ' [idLanguage]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[LocalizedName]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="usp_LocalizedName_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Inserts a record into the LocalizedName table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="nvarchar" direction="Input" size="500" precision="0" scale="0" param="(500)" nulldefault=""/>
				<parameter name="@IdLanguage" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	
				INSERT INTO [dbo].[LocalizedName]
					(
					[name]
					,[idLanguage]
					)
				VALUES
					(
					@Name
					,@IdLanguage
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_LocalizedName_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Updates a record in the LocalizedName table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="nvarchar" direction="Input" size="500" precision="0" scale="0" param="(500)" nulldefault=""/>
				<parameter name="@IdLanguage" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[LocalizedName]
				SET
					[name] = @Name
					,[idLanguage] = @IdLanguage
				WHERE
[id] = @Id 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_LocalizedName_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Deletes a record in the LocalizedName table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[LocalizedName] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_LocalizedName_GetByIdLanguage" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the LocalizedName table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdLanguage" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[name],
					[idLanguage]
				FROM
					[dbo].[LocalizedName]
				WHERE
					[idLanguage] = @IdLanguage
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_LocalizedName_GetById" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the LocalizedName table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[id],
					[name],
					[idLanguage]
				FROM
					[dbo].[LocalizedName]
				WHERE
					[id] = @Id
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_LocalizedName_Find" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Finds records in the LocalizedName table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Name" type="nvarchar" direction="Input" size="500" precision="0" scale="0" param="(500)" nulldefault="null"/>
				<parameter name="@IdLanguage" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [name]
	, [idLanguage]
    FROM
	[dbo].[LocalizedName]
    WHERE 
	 ([id] = @Id OR @Id is null)
	AND ([name] = @Name OR @Name is null)
	AND ([idLanguage] = @IdLanguage OR @IdLanguage is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [name]
	, [idLanguage]
    FROM
	[dbo].[LocalizedName]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([name] = @Name AND @Name is not null)
	OR ([idLanguage] = @IdLanguage AND @IdLanguage is not null)
	Select @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="usp_LocalizedName_Find_Dynamic" grant="" skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the LocalizedName table using a dynamically generated query.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				
				with PageIndex as (
					select top {3} row_number() over (order by {1}) as RowIndex
					, [id]
					, [name]
					, [idLanguage]
					from [dbo].[LocalizedName] {0}
				)
				select
				       [id],
				       [name],
				       [idLanguage]
				  from PageIndex
				 where RowIndex > {2}
				   and RowIndex <= {3};
				

				-- get total count
				select count(*) as TotalRowCount from [dbo].[LocalizedName] {0};
			
				END
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_ResourceType_Get_List" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets all records from the ResourceType table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[name]
				FROM
					[dbo].[ResourceType]
					
				Select @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_ResourceType_GetPaged" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the ResourceType table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[id]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [id]'
				SET @SQL = @SQL + ', [name]'
				SET @SQL = @SQL + ' FROM [dbo].[ResourceType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [id],'
				SET @SQL = @SQL + ' [name]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[ResourceType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="usp_ResourceType_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Inserts a record into the ResourceType table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="nvarchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	
				INSERT INTO [dbo].[ResourceType]
					(
					[name]
					)
				VALUES
					(
					@Name
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_ResourceType_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Updates a record in the ResourceType table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="nvarchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[ResourceType]
				SET
					[name] = @Name
				WHERE
[id] = @Id 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_ResourceType_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Deletes a record in the ResourceType table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[ResourceType] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_ResourceType_GetById" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the ResourceType table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[id],
					[name]
				FROM
					[dbo].[ResourceType]
				WHERE
					[id] = @Id
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_ResourceType_GetByName" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the ResourceType table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Name" type="nvarchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[id],
					[name]
				FROM
					[dbo].[ResourceType]
				WHERE
					[name] = @Name
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_ResourceType_Find" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Finds records in the ResourceType table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Name" type="nvarchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [name]
    FROM
	[dbo].[ResourceType]
    WHERE 
	 ([id] = @Id OR @Id is null)
	AND ([name] = @Name OR @Name is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [name]
    FROM
	[dbo].[ResourceType]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([name] = @Name AND @Name is not null)
	Select @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="usp_ResourceType_Find_Dynamic" grant="" skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the ResourceType table using a dynamically generated query.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				
				with PageIndex as (
					select top {3} row_number() over (order by {1}) as RowIndex
					, [id]
					, [name]
					from [dbo].[ResourceType] {0}
				)
				select
				       [id],
				       [name]
				  from PageIndex
				 where RowIndex > {2}
				   and RowIndex <= {3};
				

				-- get total count
				select count(*) as TotalRowCount from [dbo].[ResourceType] {0};
			
				END
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_Resource_Get_List" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets all records from the Resource table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[uri],
					[resourceType]
				FROM
					[dbo].[Resource]
					
				Select @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_Resource_GetPaged" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the Resource table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[id]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [id]'
				SET @SQL = @SQL + ', [uri]'
				SET @SQL = @SQL + ', [resourceType]'
				SET @SQL = @SQL + ' FROM [dbo].[Resource]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [id],'
				SET @SQL = @SQL + ' [uri],'
				SET @SQL = @SQL + ' [resourceType]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Resource]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="usp_Resource_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Inserts a record into the Resource table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Uri" type="nvarchar" direction="Input" size="1000" precision="0" scale="0" param="(1000)" nulldefault=""/>
				<parameter name="@ResourceType" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	
				INSERT INTO [dbo].[Resource]
					(
					[id]
					,[uri]
					,[resourceType]
					)
				VALUES
					(
					@Id
					,@Uri
					,@ResourceType
					)
				
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Resource_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Updates a record in the Resource table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@OriginalId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Uri" type="nvarchar" direction="Input" size="1000" precision="0" scale="0" param="(1000)" nulldefault=""/>
				<parameter name="@ResourceType" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Resource]
				SET
					[id] = @Id
					,[uri] = @Uri
					,[resourceType] = @ResourceType
				WHERE
[id] = @OriginalId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Resource_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Deletes a record in the Resource table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[Resource] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Resource_GetByResourceType" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Resource table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@ResourceType" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[uri],
					[resourceType]
				FROM
					[dbo].[Resource]
				WHERE
					[resourceType] = @ResourceType
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Resource_GetById" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Resource table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[id],
					[uri],
					[resourceType]
				FROM
					[dbo].[Resource]
				WHERE
					[id] = @Id
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_Resource_GetByIdCategoryFromResourcePerCategory" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdCategory" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
SELECT dbo.[Resource].[id]
       ,dbo.[Resource].[uri]
       ,dbo.[Resource].[resourceType]
  FROM dbo.[Resource]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[ResourcePerCategory] 
                WHERE dbo.[ResourcePerCategory].[idCategory] = @IdCategory
                  AND dbo.[ResourcePerCategory].[idResource] = dbo.[Resource].[id]
                  )
				Select @@ROWCOUNT			
				]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Resource_Find" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Finds records in the Resource table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Uri" type="nvarchar" direction="Input" size="1000" precision="0" scale="0" param="(1000)" nulldefault="null"/>
				<parameter name="@ResourceType" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [uri]
	, [resourceType]
    FROM
	[dbo].[Resource]
    WHERE 
	 ([id] = @Id OR @Id is null)
	AND ([uri] = @Uri OR @Uri is null)
	AND ([resourceType] = @ResourceType OR @ResourceType is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [uri]
	, [resourceType]
    FROM
	[dbo].[Resource]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([uri] = @Uri AND @Uri is not null)
	OR ([resourceType] = @ResourceType AND @ResourceType is not null)
	Select @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="usp_Resource_Find_Dynamic" grant="" skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the Resource table using a dynamically generated query.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				
				with PageIndex as (
					select top {3} row_number() over (order by {1}) as RowIndex
					, [id]
					, [uri]
					, [resourceType]
					from [dbo].[Resource] {0}
				)
				select
				       [id],
				       [uri],
				       [resourceType]
				  from PageIndex
				 where RowIndex > {2}
				   and RowIndex <= {3};
				

				-- get total count
				select count(*) as TotalRowCount from [dbo].[Resource] {0};
			
				END
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_Category_Get_List" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets all records from the Category table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[idLabel],
					[parentId]
				FROM
					[dbo].[Category]
					
				Select @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_Category_GetPaged" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the Category table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[id]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [id]'
				SET @SQL = @SQL + ', [idLabel]'
				SET @SQL = @SQL + ', [parentId]'
				SET @SQL = @SQL + ' FROM [dbo].[Category]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [id],'
				SET @SQL = @SQL + ' [idLabel],'
				SET @SQL = @SQL + ' [parentId]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Category]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="usp_Category_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Inserts a record into the Category table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@IdLabel" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@ParentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	
				INSERT INTO [dbo].[Category]
					(
					[id]
					,[idLabel]
					,[parentId]
					)
				VALUES
					(
					@Id
					,@IdLabel
					,@ParentId
					)
				
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Category_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Updates a record in the Category table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@OriginalId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@IdLabel" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@ParentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Category]
				SET
					[id] = @Id
					,[idLabel] = @IdLabel
					,[parentId] = @ParentId
				WHERE
[id] = @OriginalId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Category_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Deletes a record in the Category table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[Category] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Category_GetByParentId" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Category table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@ParentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[idLabel],
					[parentId]
				FROM
					[dbo].[Category]
				WHERE
					[parentId] = @ParentId
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Category_GetByIdLabel" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Category table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdLabel" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[idLabel],
					[parentId]
				FROM
					[dbo].[Category]
				WHERE
					[idLabel] = @IdLabel
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Category_GetById" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Category table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[id],
					[idLabel],
					[parentId]
				FROM
					[dbo].[Category]
				WHERE
					[id] = @Id
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_Category_GetByIdResourceFromResourcePerCategory" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdResource" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
SELECT dbo.[Category].[id]
       ,dbo.[Category].[idLabel]
       ,dbo.[Category].[parentId]
  FROM dbo.[Category]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[ResourcePerCategory] 
                WHERE dbo.[ResourcePerCategory].[idResource] = @IdResource
                  AND dbo.[ResourcePerCategory].[idCategory] = dbo.[Category].[id]
                  )
				Select @@ROWCOUNT			
				]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Category_Find" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Finds records in the Category table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@IdLabel" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@ParentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [idLabel]
	, [parentId]
    FROM
	[dbo].[Category]
    WHERE 
	 ([id] = @Id OR @Id is null)
	AND ([idLabel] = @IdLabel OR @IdLabel is null)
	AND ([parentId] = @ParentId OR @ParentId is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [idLabel]
	, [parentId]
    FROM
	[dbo].[Category]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([idLabel] = @IdLabel AND @IdLabel is not null)
	OR ([parentId] = @ParentId AND @ParentId is not null)
	Select @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="usp_Category_Find_Dynamic" grant="" skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the Category table using a dynamically generated query.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				
				with PageIndex as (
					select top {3} row_number() over (order by {1}) as RowIndex
					, [id]
					, [idLabel]
					, [parentId]
					from [dbo].[Category] {0}
				)
				select
				       [id],
				       [idLabel],
				       [parentId]
				  from PageIndex
				 where RowIndex > {2}
				   and RowIndex <= {3};
				

				-- get total count
				select count(*) as TotalRowCount from [dbo].[Category] {0};
			
				END
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_ResourcePerCategory_Get_List" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets all records from the ResourcePerCategory table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[idCategory],
					[idResource]
				FROM
					[dbo].[ResourcePerCategory]
					
				Select @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_ResourcePerCategory_GetPaged" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the ResourcePerCategory table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[idCategory]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [idCategory]'
				SET @SQL = @SQL + ', [idResource]'
				SET @SQL = @SQL + ' FROM [dbo].[ResourcePerCategory]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [idCategory],'
				SET @SQL = @SQL + ' [idResource]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[ResourcePerCategory]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="usp_ResourcePerCategory_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Inserts a record into the ResourcePerCategory table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdCategory" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@IdResource" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	
				INSERT INTO [dbo].[ResourcePerCategory]
					(
					[idCategory]
					,[idResource]
					)
				VALUES
					(
					@IdCategory
					,@IdResource
					)
				
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_ResourcePerCategory_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Updates a record in the ResourcePerCategory table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdCategory" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@OriginalIdCategory" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@IdResource" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@OriginalIdResource" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[ResourcePerCategory]
				SET
					[idCategory] = @IdCategory
					,[idResource] = @IdResource
				WHERE
[idCategory] = @OriginalIdCategory 
AND [idResource] = @OriginalIdResource 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_ResourcePerCategory_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Deletes a record in the ResourcePerCategory table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdCategory" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@IdResource" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[ResourcePerCategory] WITH (ROWLOCK) 
				WHERE
					[idCategory] = @IdCategory
					AND [idResource] = @IdResource
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_ResourcePerCategory_GetByIdCategory" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the ResourcePerCategory table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdCategory" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[idCategory],
					[idResource]
				FROM
					[dbo].[ResourcePerCategory]
				WHERE
					[idCategory] = @IdCategory
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_ResourcePerCategory_GetByIdResource" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the ResourcePerCategory table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdResource" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[idCategory],
					[idResource]
				FROM
					[dbo].[ResourcePerCategory]
				WHERE
					[idResource] = @IdResource
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_ResourcePerCategory_GetByIdCategoryIdResource" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the ResourcePerCategory table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdCategory" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@IdResource" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[idCategory],
					[idResource]
				FROM
					[dbo].[ResourcePerCategory]
				WHERE
					[idCategory] = @IdCategory
					AND [idResource] = @IdResource
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_ResourcePerCategory_Find" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Finds records in the ResourcePerCategory table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@IdCategory" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@IdResource" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [idCategory]
	, [idResource]
    FROM
	[dbo].[ResourcePerCategory]
    WHERE 
	 ([idCategory] = @IdCategory OR @IdCategory is null)
	AND ([idResource] = @IdResource OR @IdResource is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [idCategory]
	, [idResource]
    FROM
	[dbo].[ResourcePerCategory]
    WHERE 
	 ([idCategory] = @IdCategory AND @IdCategory is not null)
	OR ([idResource] = @IdResource AND @IdResource is not null)
	Select @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="usp_ResourcePerCategory_Find_Dynamic" grant="" skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the ResourcePerCategory table using a dynamically generated query.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				
				with PageIndex as (
					select top {3} row_number() over (order by {1}) as RowIndex
					, [idCategory]
					, [idResource]
					from [dbo].[ResourcePerCategory] {0}
				)
				select
				       [idCategory],
				       [idResource]
				  from PageIndex
				 where RowIndex > {2}
				   and RowIndex <= {3};
				

				-- get total count
				select count(*) as TotalRowCount from [dbo].[ResourcePerCategory] {0};
			
				END
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_Import_Get_List" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets all records from the Import table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[idRow],
					[catid],
					[resourcetype],
					[value],
					[title],
					[language],
					[idLabel]
				FROM
					[dbo].[Import]
					
				Select @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_Import_GetPaged" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the Import table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[idRow]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [idRow]'
				SET @SQL = @SQL + ', [catid]'
				SET @SQL = @SQL + ', [resourcetype]'
				SET @SQL = @SQL + ', [value]'
				SET @SQL = @SQL + ', [title]'
				SET @SQL = @SQL + ', [language]'
				SET @SQL = @SQL + ', [idLabel]'
				SET @SQL = @SQL + ' FROM [dbo].[Import]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [idRow],'
				SET @SQL = @SQL + ' [catid],'
				SET @SQL = @SQL + ' [resourcetype],'
				SET @SQL = @SQL + ' [value],'
				SET @SQL = @SQL + ' [title],'
				SET @SQL = @SQL + ' [language],'
				SET @SQL = @SQL + ' [idLabel]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Import]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="usp_Import_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Inserts a record into the Import table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdRow" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Catid" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Resourcetype" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Value" type="nvarchar" direction="Input" size="500" precision="0" scale="0" param="(500)" nulldefault=""/>
				<parameter name="@Title" type="nvarchar" direction="Input" size="200" precision="0" scale="0" param="(200)" nulldefault=""/>
				<parameter name="@Language" type="nvarchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault=""/>
				<parameter name="@IdLabel" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	
				INSERT INTO [dbo].[Import]
					(
					[catid]
					,[resourcetype]
					,[value]
					,[title]
					,[language]
					,[idLabel]
					)
				VALUES
					(
					@Catid
					,@Resourcetype
					,@Value
					,@Title
					,@Language
					,@IdLabel
					)
				
				-- Get the identity value
				SET @IdRow = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Import_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Updates a record in the Import table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdRow" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Catid" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Resourcetype" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Value" type="nvarchar" direction="Input" size="500" precision="0" scale="0" param="(500)" nulldefault=""/>
				<parameter name="@Title" type="nvarchar" direction="Input" size="200" precision="0" scale="0" param="(200)" nulldefault=""/>
				<parameter name="@Language" type="nvarchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault=""/>
				<parameter name="@IdLabel" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Import]
				SET
					[catid] = @Catid
					,[resourcetype] = @Resourcetype
					,[value] = @Value
					,[title] = @Title
					,[language] = @Language
					,[idLabel] = @IdLabel
				WHERE
[idRow] = @IdRow 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Import_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Deletes a record in the Import table
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdRow" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[Import] WITH (ROWLOCK) 
				WHERE
					[idRow] = @IdRow
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="usp_Import_GetByIdRow" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Import table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@IdRow" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[idRow],
					[catid],
					[resourcetype],
					[value],
					[title],
					[language],
					[idLabel]
				FROM
					[dbo].[Import]
				WHERE
					[idRow] = @IdRow
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_Import_GetByCatid" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Import table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Catid" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[idRow],
					[catid],
					[resourcetype],
					[value],
					[title],
					[language],
					[idLabel]
				FROM
					[dbo].[Import]
				WHERE
					[catid] = @Catid
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_Import_GetByLanguage" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Import table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Language" type="nvarchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[idRow],
					[catid],
					[resourcetype],
					[value],
					[title],
					[language],
					[idLabel]
				FROM
					[dbo].[Import]
				WHERE
					[language] = @Language
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_Import_GetByResourcetype" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Select records from the Import table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Resourcetype" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[idRow],
					[catid],
					[resourcetype],
					[value],
					[title],
					[language],
					[idLabel]
				FROM
					[dbo].[Import]
				WHERE
					[resourcetype] = @Resourcetype
			Select @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="usp_Import_Find" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Finds records in the Import table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@IdRow" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Catid" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Resourcetype" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Value" type="nvarchar" direction="Input" size="500" precision="0" scale="0" param="(500)" nulldefault="null"/>
				<parameter name="@Title" type="nvarchar" direction="Input" size="200" precision="0" scale="0" param="(200)" nulldefault="null"/>
				<parameter name="@Language" type="nvarchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault="null"/>
				<parameter name="@IdLabel" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [idRow]
	, [catid]
	, [resourcetype]
	, [value]
	, [title]
	, [language]
	, [idLabel]
    FROM
	[dbo].[Import]
    WHERE 
	 ([idRow] = @IdRow OR @IdRow is null)
	AND ([catid] = @Catid OR @Catid is null)
	AND ([resourcetype] = @Resourcetype OR @Resourcetype is null)
	AND ([value] = @Value OR @Value is null)
	AND ([title] = @Title OR @Title is null)
	AND ([language] = @Language OR @Language is null)
	AND ([idLabel] = @IdLabel OR @IdLabel is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [idRow]
	, [catid]
	, [resourcetype]
	, [value]
	, [title]
	, [language]
	, [idLabel]
    FROM
	[dbo].[Import]
    WHERE 
	 ([idRow] = @IdRow AND @IdRow is not null)
	OR ([catid] = @Catid AND @Catid is not null)
	OR ([resourcetype] = @Resourcetype AND @Resourcetype is not null)
	OR ([value] = @Value AND @Value is not null)
	OR ([title] = @Title AND @Title is not null)
	OR ([language] = @Language AND @Language is not null)
	OR ([idLabel] = @IdLabel AND @IdLabel is not null)
	Select @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="usp_Import_Find_Dynamic" grant="" skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the Import table using a dynamically generated query.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				
				with PageIndex as (
					select top {3} row_number() over (order by {1}) as RowIndex
					, [idRow]
					, [catid]
					, [resourcetype]
					, [value]
					, [title]
					, [language]
					, [idLabel]
					from [dbo].[Import] {0}
				)
				select
				       [idRow],
				       [catid],
				       [resourcetype],
				       [value],
				       [title],
				       [language],
				       [idLabel]
				  from PageIndex
				 where RowIndex > {2}
				   and RowIndex <= {3};
				

				-- get total count
				select count(*) as TotalRowCount from [dbo].[Import] {0};
			
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="_Import_TruncateMainTables"  skip="true">
			<comment><![CDATA[-- =============================================
-- Author:		Stephane Baudet
-- Create date: Oct. 5th, 2007
-- Description:	Truncate main tables to start from scratch before importing data
-- =============================================
]]></comment>
			<parameters>
			</parameters>
			<body><![CDATA[
				

BEGIN
	SET NOCOUNT ON;

	-- Removes all constraints
	BEGIN TRAN DROPCONS1
	ALTER TABLE Category DROP CONSTRAINT FK_Category_Category
	ALTER TABLE Category DROP CONSTRAINT Label_Category_FK1
	ALTER TABLE LocalizedName DROP CONSTRAINT Language_Label_FK1
	ALTER TABLE Resource DROP CONSTRAINT FK_Resource_ResourceType
	ALTER TABLE ResourcePerCategory DROP CONSTRAINT Category_ResourcePerCategory_FK1
	ALTER TABLE ResourcePerCategory DROP CONSTRAINT Resource_ResourcePerCategory_FK1
	COMMIT TRAN DROPCONS1

	TRUNCATE TABLE Category
	TRUNCATE TABLE LocalizedName
	TRUNCATE TABLE Resource
	TRUNCATE TABLE ResourcePerCategory
	TRUNCATE TABLE Import

	--recreates the constraints
	BEGIN TRAN ADDCONS1
	ALTER TABLE Category ADD CONSTRAINT Label_Category_FK1 FOREIGN KEY (idLabel) REFERENCES LocalizedName(id)
	ALTER TABLE Category ADD CONSTRAINT FK_Category_Category FOREIGN KEY (parentId) REFERENCES Category(id)
	ALTER TABLE LocalizedName ADD CONSTRAINT Language_Label_FK1 FOREIGN KEY (idLanguage) REFERENCES Language(id)
	ALTER TABLE Resource ADD CONSTRAINT FK_Resource_ResourceType FOREIGN KEY (resourceType) REFERENCES ResourceType(id)
	ALTER TABLE ResourcePerCategory ADD CONSTRAINT Category_ResourcePerCategory_FK1 FOREIGN KEY (idCategory) REFERENCES Category(id)
	ALTER TABLE ResourcePerCategory ADD CONSTRAINT Resource_ResourcePerCategory_FK1 FOREIGN KEY (idResource) REFERENCES Resource(id)
	COMMIT TRAN ADDCONS1
END

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="_Import_PostProcess"  skip="true">
			<comment><![CDATA[]]></comment>
			<parameters>
			</parameters>
			<body><![CDATA[
				

BEGIN

-- UPDATE STATISTICS FOR THE FRESHLY IMPORTED DATA
UPDATE STATISTICS IMPORT WITH FULLSCAN, ALL

DECLARE @IDENGLISH INT
SELECT @IDENGLISH = ID FROM LANGUAGE
WHERE NAME = 'ENGLISH'

--FIRST INSERT LOCALIZED LABELS
--Nota : we can remove temporarily the foreign key to optimize the batch load
-- as we're sure that the idlanguage is correctly set
ALTER TABLE LocalizedName DROP CONSTRAINT Language_Label_FK1

INSERT LOCALIZEDNAME (NAME, IDLANGUAGE)
	SELECT DISTINCT I.VALUE, ISNULL(L.ID, @IDENGLISH)
	FROM IMPORT I 
		LEFT JOIN LANGUAGE L
		ON I.LANGUAGE = L.NAME

ALTER TABLE LocalizedName ADD CONSTRAINT Language_Label_FK1 FOREIGN KEY (idLanguage) REFERENCES Language(id)

--THEN WE GET BACK THE ID OF LABELS
UPDATE IMPORT WITH (TABLOCKX)
	SET IDLABEL = L.ID
	FROM IMPORT I JOIN LOCALIZEDNAME L
	ON I.VALUE = L.NAME
	 
--NOW, WE CAN FILL THE TABLE CATEGORY
DECLARE @TOPIC INT
DECLARE @SQL NVARCHAR(500)
SELECT @TOPIC = ID FROM RESOURCETYPE WHERE NAME = 'TOPIC'

SET @SQL = 'INSERT CATEGORY
	SELECT CATID, IDLABEL, NULL
	FROM IMPORT
	WHERE RESOURCETYPE = @TOPIC'
EXECUTE sp_executesql @SQL, N'@TOPIC INT', @TOPIC=@TOPIC

--WE RETRIEVE THE HIERARCHY BETWEEN CATEGORIES
SET @SQL ='UPDATE CATEGORY WITH (TABLOCKX)
	SET PARENTID = PARENT.CATID
	FROM CATEGORY JOIN IMPORT I ON CATEGORY.ID=I.CATID AND I.RESOURCETYPE=@TOPIC
		JOIN IMPORT PARENT ON
		PARENT.VALUE = SUBSTRING(I.VALUE , 0, LEN(I.VALUE)-PATINDEX(''%/%'',REVERSE(I.VALUE))+1)
		AND PARENT.RESOURCETYPE=@TOPIC'
EXECUTE sp_executesql @SQL, N'@TOPIC INT', @TOPIC=@TOPIC


--NOW, WE CAN PROCESS RESOURCES
SET @SQL = 'INSERT RESOURCE WITH (TABLOCKX)
	SELECT IDROW, VALUE, RESOURCETYPE
	FROM IMPORT
	WHERE RESOURCETYPE <> @TOPIC'
EXECUTE sp_executesql @SQL, N'@TOPIC INT', @TOPIC=@TOPIC

--AND ASSOCIATE RESOURCES AND CATEGORIES
SET @SQL = 'INSERT RESOURCEPERCATEGORY
	SELECT DISTINCT CATID, IDROW
	FROM IMPORT
	WHERE RESOURCETYPE <> @TOPIC'
EXECUTE sp_executesql @SQL, N'@TOPIC INT', @TOPIC=@TOPIC

--IT'S DONE !
--WE CAN CHECK DATA WITH THIS QUERY :
/*
select top 20 c.id, l.name, c.parentid, p.name, r.uri, rt.name
from category c join localizedname l on c.idlabel = l.id
join category cp on c.parentid = cp.id
join localizedname p on cp.idlabel = p.id
join resourcepercategory rpc on c.id=rpc.idcategory
join resource r on rpc.idresource = r.id
join resourcetype rt on r.resourcetype = rt.id
where c.parentid=24
*/

END
			]]></body>
					</procedure>
		<procedure owner="dbo" name="usp_CategoryWithLocalizedName_Get_List" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets all records from the CategoryWithLocalizedName view
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				SELECT
					[id],
					[idLabel],
					[parentId],
					[LocalizedName],
					[idLanguage],
					[LanguageName]
				FROM
					[dbo].[CategoryWithLocalizedName]
					
				Select @@ROWCOUNT			
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="usp_CategoryWithLocalizedName_Get" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the CategoryWithLocalizedName view passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>			
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN

				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[id]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [id]'
				SET @SQL = @SQL + ', [idLabel]'
				SET @SQL = @SQL + ', [parentId]'
				SET @SQL = @SQL + ', [LocalizedName]'
				SET @SQL = @SQL + ', [idLanguage]'
				SET @SQL = @SQL + ', [LanguageName]'
				SET @SQL = @SQL + ' FROM [dbo].[CategoryWithLocalizedName]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [id],'
				SET @SQL = @SQL + ' [idLabel],'
				SET @SQL = @SQL + ' [parentId],'
				SET @SQL = @SQL + ' [LocalizedName],'
				SET @SQL = @SQL + ' [idLanguage],'
				SET @SQL = @SQL + ' [LanguageName]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL

				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[CategoryWithLocalizedName]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
				
				END
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="usp_CategoryWithLocalizedName_Find_Dynamic" grant="" skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------

-- Created By: BackPackMan (www.BackPackMan.com)
-- Purpose: Gets records from the CategoryWithLocalizedName table using a dynamically generated query.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
				BEGIN
				
				with PageIndex as (
					select top {3} row_number() over (order by {1}) as RowIndex
					, [id]
					, [idLabel]
					, [parentId]
					, [LocalizedName]
					, [idLanguage]
					, [LanguageName]
					from [dbo].[CategoryWithLocalizedName] {0}
				)
				select
				       [id],
				       [idLabel],
				       [parentId],
				       [LocalizedName],
				       [idLanguage],
				       [LanguageName]
				  from PageIndex
				 where RowIndex > {2}
				   and RowIndex <= {3};

				-- get total count
				select count(*) as TotalRowCount from [dbo].[CategoryWithLocalizedName] {0};
				
				END
			]]></body>
		</procedure>
		
</procedures>
</root>
